﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Text.RegularExpressions;

namespace TestJQGrid
{
    public sealed class TMReports
    {
        #region make it singleton

        public static TMReports Instance
        {
            get
            {
                return Nested.instance;
            }
        }

        class Nested
        {
            // Explicit static constructor to tell C# compiler
            // not to mark type as beforefieldinit
            static Nested() { }

            internal static readonly TMReports instance = new TMReports();
        }

        #endregion

        #region initialize - load/reload
        private int ReportId;
        private DataTable dt;
        private DataTable newDt;
        private bool createNew = false;
        private int fieldCount = 7;
        private GenerateSqlStatement sqlStmt;
        private string ReportName = "";
        private string EmailID = "";
        private string ReportType = "Private";
        private string frequency = "Monthly";

        TMReports()
        {
            ReportId = 1;
            dt = new DataTable();
            RetrieveData();
        }

        private void RetrieveData()
        {
            string select = "SELECT [FieldId], [DisplayType], [Field], [DisplayName], [Operator], [Value], [DispAggr], [FiltAggr], ";
            select += " [FiltOperator], [FiltValue] FROM [ReportFields] WHERE ([ReportId] = '" + ReportId.ToString() + "')";

            dt = GlobalMethods.ExecuteSelectQuery(select);
            newDt = dt.Clone();
            sqlStmt = new GenerateSqlStatement();

            string selectName = " SELECT [ReportName],[EmailId],[Type],[Frequency]  from [Reports] where [ReportId] = '" + ReportId.ToString() + "'";
            DataTable reportDetails = GlobalMethods.ExecuteSelectQuery(selectName);

            ReportName = reportDetails.Rows[0]["ReportName"].ToString();
            EmailID = reportDetails.Rows[0]["EmailId"].ToString();
            ReportType = reportDetails.Rows[0]["Type"].ToString();
            frequency = reportDetails.Rows[0]["Frequency"].ToString();
        }

        public void Reload(int id)
        {
            ReportId = id;
            RetrieveData();
        }
        #endregion

        #region getter-setter
        public DataTable getData()
        {
            if (createNew)
                return newDt;
            return dt;
        }

        public bool getCreateState()
        {
            return createNew;
        }

        public int getReportId()
        {
            return ReportId;
        }

        public int getRowCount()
        {
            return getData().Rows.Count;
        }

        public string getReportName()
        {
            if (createNew) return "";
            return ReportName;
        }

        public string getEmailID()
        {
            return EmailID;
        }

        public string getType()
        {
            return ReportType;
        }

        public string getFrequency()
        {
            return frequency;
        }

        public void setEmailId(string email)
        {
            EmailID = email;
        }

        public void setType(string val)
        {
            ReportType = val;
        }

        public void setFrequency(string val)
        {
            frequency = val;
        }
        #endregion

        #region database update
        public void UpdateDB(string reportName)
        {
            //get sql query
            generateQuery();

            //insert in db
            updateReports(reportName);
            updateReportFields();

            //reset internals
            ReportName = reportName;
            Reload(ReportId);
        }

        private void generateQuery()
        {
            DataTable dt2 = getData();
            foreach (DataRow dr in dt2.Rows)
            {
                //retrieve values
                string entryType = dr[1].ToString();
                string columnName = dr[2].ToString();
                string displayName = dr[3].ToString();
                string oper = dr[4].ToString();
                string value = dr[5].ToString();
                string groupBy = dr[6].ToString();

                //get valid display name
                if (String.IsNullOrWhiteSpace(displayName)) displayName = columnName.Split('.')[1];
                displayName = displayName.Replace(" ","_").Trim();

                //generate sql statement
                if (entryType == "WHERE")
                {
                    sqlStmt.addCondition(columnName, oper, value);
                    continue;
                }
                if (entryType == "GROUP BY")
                {
                    sqlStmt.addGroup(columnName, displayName);
                    continue;
                }
                if (entryType == "SELECT")
                {
                    if (!String.IsNullOrEmpty(oper) && !String.IsNullOrEmpty(value))
                    {
                        value = "'" + value + "'";
                        sqlStmt.addCondition(columnName, oper, value);
                    }
                    if (!String.IsNullOrEmpty(groupBy))
                        sqlStmt.addSelect(columnName, displayName, groupBy );
                    else
                        sqlStmt.addSelect(columnName, displayName);
                    continue;
                }
            }
        }

        private void updateReports(string reportName)
        {
            string productName = "CarSales";
            if (createNew)
            {
                string insertStmt = "INSERT INTO [Reports] ([ReportName], [ProductName], [SqlQuery], [EmailId], [Type], [Frequency]) VALUES ";
                insertStmt += " ( '" + reportName + "','" + productName + "','" + sqlStmt.getQuery() + "','" + EmailID + "','" + ReportType + "','" + frequency + "' ) ";

                ReportId = GlobalMethods.ExecuteIdentityInsert(insertStmt, "ReportId");
            }
            else
            {
                string updateStmt = "UPDATE [Reports] SET [ReportName] = '" + reportName + "', [ProductName] = '" + productName + "', ";
                updateStmt += " [SqlQuery] = '" + sqlStmt.getQuery() + "', [EmailId] = '" + EmailID + "', [Type] = '" + ReportType + "', [Frequency] = '" + frequency;
                updateStmt += "' WHERE ( [ReportId] = '" + ReportId.ToString() + "')";

                GlobalMethods.ExecuteNonQuery(updateStmt);
            }
        }

        private void updateReportFields()
        {
            //update report-fields table now
            DataTable dt2 = getData();

            //set the display name
            for (int i = 0; i < dt2.Rows.Count; i++)
            {
                string name = dt2.Rows[i][3].ToString();
                if (String.IsNullOrWhiteSpace(name))
                    name = dt2.Rows[i][2].ToString().Split('.')[1];
                name = name.Replace(" ", "_").Trim();
                dt2.Rows[i][3] = name;
            }

            //set row/field number
            for (int i = 0; i < dt2.Rows.Count; i++)
                dt2.Rows[i][0] = i.ToString();

            //add report id column in start
            DataColumn dc = dt2.Columns.Add("ReportId");
            dc.SetOrdinal(0);
            for (int i = 0; i < dt2.Rows.Count; i++)
            {
                dt2.Rows[i][0] = ReportId;
            }

            //delete and then insert in db
            string deleteQ = "DELETE FROM [ReportFields] WHERE (ReportId = '" + ReportId.ToString() + "')";
            GlobalMethods.ExecuteNonQuery(deleteQ);

            //upload datatable to db
            GlobalMethods.uploadDT2DB(dt2, "ReportFields");
        }
        #endregion

        #region DataTable operations
        public void CreatingNew(bool cn)
        {
            createNew = cn;
            if (createNew)
                newDt.Clear();
        }

        public bool addRow(System.Collections.Specialized.NameValueCollection data)
        {
            if (!checkValidRow(data)) return false;

            //read the values of each field(join is dummy)
            DataTable dt2 = getData();
            DataRow dr = dt2.NewRow();
            for (int i = 0; i < fieldCount; i++)
                dr[i + 1] = String.Join(",", data.GetValues(i));
            dt2.Rows.Add(dr);
            dt2.AcceptChanges();

            //set row number
            for (int i = 0; i < dt2.Rows.Count; i++)
                dt2.Rows[i][0] = i.ToString();
            dt2.AcceptChanges();

            return true;
        }

        public void deleteRow(int fieldId)
        {
            DataTable dt2 = getData();
            dt2.Rows[fieldId].Delete();
            dt2.AcceptChanges();

            //set row number
            for (int i = 0; i < dt2.Rows.Count; i++)
                dt2.Rows[i][0] = i.ToString();
            dt2.AcceptChanges();
        }

        public bool editRow(int fieldId, System.Collections.Specialized.NameValueCollection data)
        {
            if (!checkValidRow(data)) return false;

            DataTable dt2 = getData();
            for (int i = 0; i < fieldCount; i++)
                dt2.Rows[fieldId][i + 1] = String.Join(",", data.GetValues(i));
            dt2.AcceptChanges();

            return true;
        }
        #endregion

        #region DataTable operations - validation
        private bool checkValidRow(System.Collections.Specialized.NameValueCollection data)
        {
            string entryType = String.Join(",", data.GetValues(0));
            if (entryType == "SELECT")
            {
                if (!check_select(data)) return false;
            }
            else if (entryType == "GROUP BY")
            {
                if (!check_groupBy(data)) return false;
            }
            else if (entryType == "WHERE")
            {
                if (!check_where(data)) return false;
            }
            else
            {
                return false;
            }
            return true;
        }

        private static bool isAlphaNumeric(string str)
        {
            return Regex.Match(str.Trim(), @"^[a-zA-Z0-9]*$").Success;
        }

        private bool check_select(System.Collections.Specialized.NameValueCollection data)
        {
            //retrieve values
            //string entryType = String.Join(",", data.GetValues(0));
            //string columnName = String.Join(",", data.GetValues(1));
            string displayName  = String.Join(",", data.GetValues(2));
            string oper = String.Join(",", data.GetValues(3));
            string value = String.Join(",", data.GetValues(4));
            //string groupBy = String.Join(",", data.GetValues(5));

            //check where part - if atleast one of them(oper/value) is populated
            if ((!String.IsNullOrEmpty(oper)) || (!String.IsNullOrEmpty(value)))
            {
                //then both must be populated
                if (String.IsNullOrEmpty(oper) || String.IsNullOrEmpty(value))
                    return false;
            }

            //display name should be only alphanumeric
            if (!isAlphaNumeric(displayName)) return false;

            return true;
        }

        private bool check_where(System.Collections.Specialized.NameValueCollection data)
        {
            //retrieve values
            //string entryType = String.Join(",", data.GetValues(0));
            //string columnName = String.Join(",", data.GetValues(1));
            //string displayName  = String.Join(",", data.GetValues(2));
            string oper = String.Join(",", data.GetValues(3));
            string value = String.Join(",", data.GetValues(4));
            string groupBy = String.Join(",", data.GetValues(5));

            //both (oper & value)must be populated
            if (String.IsNullOrEmpty(oper) || String.IsNullOrEmpty(value))
                return false;

            //groupBy cannot be populated for where part
            if (!String.IsNullOrEmpty(groupBy))
                return false;

            return true;
        }

        private bool check_groupBy(System.Collections.Specialized.NameValueCollection data)
        {
            //retrieve values
            //string entryType = String.Join(",", data.GetValues(0));
            //string columnName = String.Join(",", data.GetValues(1));
            string displayName  = String.Join(",", data.GetValues(2));
            string oper = String.Join(",", data.GetValues(3));
            string value = String.Join(",", data.GetValues(4));
            //string groupBy = String.Join(",", data.GetValues(5));

            if (!String.IsNullOrEmpty(oper) || !String.IsNullOrEmpty(value))
                return false;

            //display name should be only alphanumeric
            if (!isAlphaNumeric(displayName)) return false;

            return true;
        }
        #endregion
    }
}